﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using CustomsSystem.Model;
using Maticsoft.DBUtility;

namespace CustomsSystem.DAL
{
    public class T_AllService
    {
        public DataSet GetList(DateTime strKAI, DateTime strMO, string Cname)
        {
            //应付实付
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from (select sheetid,shipname,customername,goodsname,agencydate,contractno from T_sheetinfo a    ");
            strSql.Append(" left join  cu_customer b on (b.id=a.cid)) sheet   ");
            strSql.Append("left join (select distinct(c.sheetid),e.feetype,c.billTypeID,c.payedid,sum(c.sfeeamount)over(PARTITION BY  c.sheetid) as Yfu,sum(c.afeeamount)over(PARTITION BY  c.sheetid) as Sfu ");
            strSql.Append(" from T_bill  c left join info_feetype e on (e.feetypeno=c.feetypeid) ");

            strSql.Append(" where c.billtypeid='2' and payedid='1')Y on (Y.sheetid=sheet.sheetid) ");
            strSql.Append("   where payedid='1' ");
          
            if (strKAI != null)
            {
                strSql.Append("and agencydate>= '" + strKAI + "'");
            }
            if (strMO != null)
            {
                strSql.Append(" and agencydate <= '" + strMO + "'");
            }
            if (Cname != null)
            {
                strSql.Append(" and customername like '%" + Cname + "%'");
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetList1(DateTime strKAI, DateTime strMO, string Cname)
        {
            //应收实收
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from (select sheetid,shipname,customername,goodsname,agencydate,contractno from T_sheetinfo a    ");
            strSql.Append(" left join  cu_customer b on (b.id=a.cid)) sheet   ");
            strSql.Append("left join (select distinct(c.sheetid),e.feetype,c.billTypeID,c.payedid,sum(c.sfeeamount)over(PARTITION BY  c.sheetid) as Yshou,sum(c.afeeamount)over(PARTITION BY  c.sheetid) as Sshou ");
            strSql.Append(" from T_bill  c left join info_feetype e on (e.feetypeno=c.feetypeid) ");

            strSql.Append(" where c.billtypeid='1' and payedid='1')Y on (Y.sheetid=sheet.sheetid) ");
            strSql.Append("   where payedid='1' ");

            if (strKAI != null)
            {
                strSql.Append("and agencydate>= '" + strKAI + "'");
            }
            if (strMO != null)
            {
                strSql.Append(" and agencydate <= '" + strMO + "'");
            }
            if (Cname != null)
            {
                strSql.Append(" and customername like '%" + Cname + "%'");
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetListLirun(DateTime strKAI, DateTime strMO, string Cname, string payedID)
        {
            //利润统计
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from (select sheetid,shipname,customername,grossWeight,goodsname,agencydate,contractno,a.RegisterUnit,a.containerNo,a.NO   from T_sheetinfo a ");
            strSql.Append(" left join  cu_customer b on (b.id=a.cid)) sheet   ");
            strSql.Append(" left join (select distinct(c.sheetid),c.billTypeID,c.payedid,");
            strSql.Append("  sum(c.sfeeamount)over(PARTITION BY  c.sheetid) as Sshou  from T_bill  c  where ");

            strSql.Append(" c.billtypeid='1')S on (S.sheetid=sheet.sheetid) ");

            strSql.Append(" left join (select distinct(c.sheetid),c.billTypeID, ");
            strSql.Append("  sum(c.sfeeamount)over(PARTITION BY  c.sheetid) as Sfu from T_bill  c   ");

            strSql.Append("   where c.billtypeid='2' )F on (F.sheetid=sheet.sheetid) where ");
            if (payedID != null)
            {
                strSql.Append(" payedID= '" + payedID + "'");
            }
            if (strKAI != null)
            {
                strSql.Append(" and agencydate>= '" + strKAI + "'");
            }
            if (strMO != null)
            {
                strSql.Append(" and agencydate <= '" + strMO + "'");
            }
            if (Cname != null)
            {
                strSql.Append(" and customername like '%" + Cname + "%'");
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetFeeWS(DateTime strKAI, DateTime strMO,string user ,string payedID)
        {
            //收费清单
            StringBuilder strSql = new StringBuilder();


            strSql.Append("select distinct(billid),T_sheetInfo.sheetid,ladingBillNo,CustomerName,shipName,grossWeight,goodsName,agencyDate,feeName,sfeeAmount,afeeAmount,sfeeAmount-afeeAmount '差额'");
            strSql.Append(" from T_bill,T_sheetInfo,Info_code,Cu_Customer,Info_feeType   ");
            strSql.Append(" where T_sheetInfo.cID=Cu_Customer.ID and T_bill.sheetID=T_sheetInfo.sheetID");
            strSql.Append(" and Info_code.codeType='goodtype' ");
            strSql.Append(" and Info_feeType.feeTypeNo=T_bill.feeTypeID and billTypeID='1' ");

            if (payedID != null)
            {
                strSql.Append(" and ( payedID=  '" + payedID + "' or payedID=  '2')");
            }
            if (user != null)
            {
                strSql.Append(" and CustomerName like '%" + user + "%'");
            }
            if (strKAI != null)
            {
                strSql.Append("and agencydate>= '" + strKAI + "'");
            }
            if (strMO != null)
            {
                strSql.Append(" and agencydate <= '" + strMO + "'");
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetFeeWF(DateTime strKAI, DateTime strMO, string user, string payedID)
        {

            //欠费清单
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct(billid),ladingBillNo,CustomerName,shipName,goodsName,agencyDate,feeName,sfeeAmount,afeeAmount,sfeeAmount-afeeAmount '差额'");
            strSql.Append(" from T_bill,T_sheetInfo,Info_code,Cu_Customer,Info_feeType   ");
            strSql.Append(" where T_sheetInfo.cID=Cu_Customer.ID and T_bill.sheetID=T_sheetInfo.sheetID");
            strSql.Append(" and Info_code.codeType='goodtype' ");
            strSql.Append(" and Info_feeType.feeTypeNo=T_bill.feeTypeID and billTypeID='2' ");

            if (payedID != null)
            {
                strSql.Append(" and payedID=  '" + payedID + "'");
            }
            if (user != null)
            {
                strSql.Append(" and CustomerName like '%" + user + "%'");
            }
            if (strKAI != null)
            {
                strSql.Append("and agencydate>= '" + strKAI + "'");
            }
            if (strMO != null)
            {
                strSql.Append(" and agencydate <= '" + strMO + "'");
            }

            return DbHelperSQL.Query(strSql.ToString());
        }
    }
}
